import pandas as pd

# 读取 Excel 文件
excel_file = 'your_file.xlsx'  # 请替换为你的 Excel 文件路径
data = pd.read_excel(excel_file)

# 数据库插入脚本
insert_query_template = """
INSERT INTO firewall (
    assets_id, sn, name, vendor, brand, model, unit_size, rack_no,
    rack_unit_position, data_center_code, expiration_time, launched_time,
    os, major_version, patch_version, bmc_ip, ips, snat_ips, dnat_ips,
    bandwidth, ops_bandwidth, label, purchase_time
) VALUES (
    '{assets_id}', '{sn}', '{name}', '{vendor}', '{brand}', '{model}', {unit_size}, '{rack_no}',
    '{rack_unit_position}', {data_center_code}, '{expiration_time}', '{launched_time}',
    '{os}', '{major_version}', '{patch_version}', '{bmc_ip}', '{ips}', '{snat_ips}', '{dnat_ips}',
    '{bandwidth}', '{ops_bandwidth}', '{label}', '{purchase_time}'
);
"""

# 生成 SQL 语句
def generate_sql(data):
    sql_statements = []
    for index, row in data.iterrows():
        sql = insert_query_template.format(
            assets_id=row['assets_id'], sn=row['sn'], name=row['name'], vendor=row['vendor'], brand=row['brand'], model=row['model'],
            unit_size='NULL' if pd.isna(row['unit_size']) else int(row['unit_size']),
            rack_no=row['rack_no'], rack_unit_position=row['rack_unit_position'], data_center_code='NULL' if pd.isna(row['data_center_code']) else int(row['data_center_code']),
            expiration_time=row['expiration_time'], launched_time=row['launched_time'], os=row['os'],
            major_version=row['major_version'], patch_version=row['patch_version'], bmc_ip=row['bmc_ip'],
            ips=row['ips'], snat_ips=row['snat_ips'], dnat_ips=row['dnat_ips'],
            bandwidth=row['bandwidth'], ops_bandwidth=row['ops_bandwidth'], label=row['label'],
            purchase_time=row['purchase_time']
        )
        sql_statements.append(sql)
    return sql_statements

# 获取生成的 SQL 语句
sql_statements = generate_sql(data)

# 打印 SQL 语句
for sql in sql_statements:
    println(sql)